<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Regions extends CI_Model {

	function __construct() {
        parent::__construct();
    }

    public function exists($regionGroup, $region) {
    	$exists = false;

    	$id = self::getId($regionGroup, $region);
    	$exists = ($id > 0);

    	return $exists;
    }

    public function getAll() {
    	$sql = "SELECT " .
			   "r.RegionId AS `Id`," .
			   "r.RegionGroupId," .
			   "rg.RegionGroup," .
			   "r.Region," .
			   "r.Description " .
			   "FROM " .
			   "regiongroups AS rg ". 
			   "INNER JOIN regions AS r ON rg.RegionGroupId = r.RegionGroupId " .
			   "WHERE " .
			   "(rg.CountryId = " . COUNTRY_ID . ") " .
			   "ORDER BY " .
			   "r.Region";

		$query = $this->db->query($sql);
		return $query->result_array();
    }

    public function getId($regionGroup, $region) {
    	$id = 0;

    	$sql = "SELECT " .
    		   "r.RegionId AS `Id` " .
    		   "FROM " .
    		   "`regions` AS r " .
    		   "WHERE " .
    		   "(r.RegionGroupId = " . $regionGroup . ") AND " .
    		   "(r.Region LIKE " . $this->db->escape((string)$region) . ")";

    	$query = $this->db->query($sql);
    	
    	if ($query->num_rows() > 0) {
    		$row = $query->row_array();
    		$id = $row['Id'];
    	}

    	return $id;
    }

    public function save($regions) {
    	$saved = true;

    	$this->db->trans_begin();

    	foreach ($regions as $region) {
    		$sql = '';
    		$params = array();

    		if ($region->RowState == 'added') {
    			$sql = "INSERT INTO `regions`" .
    				   "(`RegionGroupId`, `Region`, `Description`, `DateCreated`) " .
    				   "VALUES " .
    				   "(?, ?, ?, NOW())";

    			$params = array( $region->RegionGroup, $region->Region, $region->Description);
    		}
    		else if ($region->RowState == 'modified') {
    			$sql = "UPDATE `regions` SET " .
    				   "`RegionGroupId` = ?, `Region` = ?, `Description` = ? " .
    				   "WHERE " .
    				   "(`RegionId` = ?)";

    			$params = array( $region->RegionGroup, $region->Region, $region->Description, $region->Id ); 
    		}
    		else if ($region->RowState == 'deleted') {
    			$sql = "DELETE FROM `regions` WHERE (`RegionId` = ?)";
    			$params = array($region->Id);
    		}
    		else {

    		}

    		if (trim($sql) != '') $this->db->query($sql, $params);
    	}

    	if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $saved = false;
        }
        else {
            $this->db->trans_commit();
        }

    	return $saved;
    }

}